/**
 * Copyright (c) 2019 Coder League
 * All rights reserved.
 *
 * File：GoodSalesDaoImpl.java
 * History:
 *         2019年6月7日: Initially created, wangjb.
 */
package club.coderleague.ilsp.dao.impl;

import java.util.Arrays;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.lang3.StringUtils;

import club.coderleague.data.jpa.domain.Page;
import club.coderleague.data.jpa.id.support.SnowflakeV4;
import club.coderleague.data.jpa.repository.support.AbstractDataRepositoryExtension;
import club.coderleague.ilsp.common.domain.beans.GoodSaleSettleIdExtension;
import club.coderleague.ilsp.common.domain.beans.GoodSalesExtension;
import club.coderleague.ilsp.common.domain.beans.mobile.SingleGoodsExtension;
import club.coderleague.ilsp.common.domain.enums.EntityState;
import club.coderleague.ilsp.common.util.CommonUtil;
import club.coderleague.ilsp.dao.extension.GoodSalesDaoExtension;
import club.coderleague.ilsp.entities.Goodsales;

/**
 * 商品销售表数据访问扩展对象实现。
 * @author wangjb
 */
public class GoodSalesDaoImpl extends AbstractDataRepositoryExtension<Goodsales, Long, SnowflakeV4> implements GoodSalesDaoExtension {

	/**
	 * @see club.coderleague.ilsp.dao.extension.GoodSalesDaoExtension#getGoodSalesMgrDao(java.util.Map)
	 */
	@Override
	public Page<GoodSalesExtension> getGoodSalesMgrDao(Map<String, Object> params) throws Exception{
		Map<String, Object> param = new HashMap<String, Object>();
		String qsql = "select m2.entityid as marketid, m.entityid as mid, m.merchantname, m2.marketname as marketname, ms.entityid as settleid,(select count(g.entityid) from Goodsales g where g.settleid = ms.entityid and " + 
				"g.entitystate = :entitystate) as validNum, (select count(g.entityid) from goodsales g where g.settleid = ms.entityid and " + 
				"g.entitystate = " + EntityState.PUBLISHED.getValue() + ") as putawayNum from Goodsales gs, Merchantsettles ms, Merchants m, Markets m2 where " + 
				"gs.settleid = ms.entityid and ms.marketid = m2.entityid and ms.merchantid = m.entityid and gs.entitystate in (:state) and ms.entitystate = :entitystate and m.entitystate = :entitystate and m2.entitystate = :entitystate ";
		String csql = "select count(*) from (select count(ms.entityid) as coun from Goodsales gs, Merchantsettles ms, Merchants m, Markets m2 where " + 
				"gs.settleid = ms.entityid and ms.marketid = m2.entityid and ms.merchantid = m.entityid and gs.entitystate in (:state) and ms.entitystate = :entitystate and m.entitystate = :entitystate and m2.entitystate = :entitystate ";
		param.put("entitystate", EntityState.VALID.getValue());
		param.put("state", Arrays.asList(new Integer[] {EntityState.VALID.getValue(), EntityState.PUBLISHED.getValue()}));
		
		if (params.get("keyword") != null && StringUtils.isNotBlank(params.get("keyword").toString())) {
			qsql += "and m.entityid = :mid ";
			csql += "and m.entityid = :mid ";
            param.put("mid", Long.valueOf(params.get("keyword").toString()));
        }
		
		qsql += "group by m2.entityid, m.entityid, m.merchantname, m2.marketname, ms.entityid order by m.createtime desc ";
		csql += "group by m2.entityid, m.entityid, m.merchantname, m2.marketname, ms.entityid ) as pp ";
		
		return super.queryCustomBeanPageBySql(GoodSalesExtension.class, csql, qsql, Integer.parseInt(params.get("pageIndex").toString()), Integer.parseInt(params.get("pageSize").toString()), param);
	}
	
	/**
	 * @see club.coderleague.ilsp.dao.extension.GoodSalesDaoExtension#getGoodSalesBySettleidDao(java.lang.Long)
	 */
	@Override
	public List<Map<String, Object>> getGoodSalesBySettleidDao(Long settleid) {
		String sql = "select gs.entityid, gs.settleid, gs.specid, g.goodname as pname, gg.specdefine as goodname, gg.meterunit as goodorigin, gs.entitystate, g.entityid as pid, gg.entityid as id, g.goodorigin as goodorigins from Goodsales gs, Goodspecs gg, Goods g "
				+ "where gs.specid = gg.entityid and gg.goodid = g.entityid and gs.settleid = ?0 and gs.entitystate in (?1) and gg.entitystate = ?2 and g.entitystate = ?3 ";
		return super.queryMapListBySql(sql, settleid, Arrays.asList(new Integer[] {EntityState.VALID.getValue(), EntityState.PUBLISHED.getValue()}), EntityState.VALID.getValue(), EntityState.VALID.getValue());
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.GoodSalesDaoExtension#getSettleIdDao()
	 */
	@Override
	public List<GoodSaleSettleIdExtension> getSettleIdDao() {
		String sql = "select mc.entityid as mid, mc.merchantname, ms.entityid as settleid, m.marketname as marketname from Merchants mc, Merchantsettles ms, Markets m where mc.entityid = ms.merchantid and "
				+ "ms.marketid = m.entityid and mc.entitystate = ?0 and ms.entitystate = ?1 and m.entitystate = ?2 ";
		return super.queryCustomBeanListBySql(GoodSaleSettleIdExtension.class, sql, EntityState.VALID.getValue(), EntityState.VALID.getValue(), EntityState.VALID.getValue());
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.GoodSalesDaoExtension#getGoodByMerchantIdDao(Long, Long)
	 */
	@Override
	public List<Map<String, Object>> getGoodByMerchantIdDao(Long mid, Long settleid) {
		Map<String, Object> param = new HashMap<String, Object>();
		String sql = "select g.entityid as id, g.goodname as goodname, g.groupid as pid,goodorigin as goodorigin, 1 as istate, '' as pname, '' as goodorigins from Goods g, Goodgroups gg where g.GroupId = gg.EntityId and g.entitystate = :entitystate and gg.entitystate = :entitystate and g.merchantid = :mid " +
				"and g.entityid in (select GoodId from GoodSpecs where EntityState = 1 and entityid not in (select gs.SpecId from Goodsales gs, Goodspecs gg where gs.specid = gg.entityid and gs.settleid = :settleid and gs.entitystate in (:istate) and gg.entitystate = :entitystate))" + 
				"union select gs.entityid, gs.specdefine, gs.goodid, gs.meterunit, 2 as istate, g.goodname, g.goodorigin from Goodspecs gs, Goods g where  gs.goodid = g.entityid and g.entitystate = :entitystate and g.merchantid = :mid and gs.entitystate in (:istate) " +
				"and gs.EntityId not in (select gs.SpecId from Goodsales gs, Goodspecs gg where gs.specid = gg.entityid and gs.settleid = :settleid and gs.entitystate in (:istate) and gg.entitystate = :entitystate)";
		param.put("entitystate", EntityState.VALID.getValue());
		param.put("istate", Arrays.asList(new Integer[] {EntityState.VALID.getValue(), EntityState.PUBLISHED.getValue()}));
		param.put("mid", mid);
		param.put("settleid", settleid);
		return super.queryMapListBySql(sql, param);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.GoodSalesDaoExtension#getGoodSalesDetailBySettleidDao(java.util.Map)
	 */
	@Override
	public List<Map<String, Object>> getGoodSalesDetailBySettleidDao(Map<String, Object> params) {
		Map<String, Object> param = new HashMap<String, Object>();
		String sql = "select gs.entityid, gs.settleid, gs.specid, g.goodname, g.goodorigin, gg.specdefine, gg.meterunit, gs.entitystate, gs.goodserials, gs.goodnumber, gs.goodprice, gs.goodstock from Goodsales gs, Goodspecs gg, Goods g "
				+ "where gs.specid = gg.entityid and gg.goodid = g.entityid and gs.settleid = :settleid and gs.entitystate in (:istate) and gg.entitystate = :entitystate and g.entitystate = :entitystate ";
		param.put("entitystate", EntityState.VALID.getValue());
		param.put("istate", Arrays.asList(new Integer[] {EntityState.VALID.getValue(), EntityState.PUBLISHED.getValue()}));
		param.put("settleid", (String) params.get("settleid"));
		
		if (params.get("keyword") != null && StringUtils.isNotBlank(params.get("keyword").toString())) {
			sql += "and (gg.specdefine like :specdefine or gg.meterunit like :meterunit or g.goodname like :goodname or g.goodorigin like :goodorigin) ";
            param.put("specdefine", "%" + (String) params.get("keyword") + "%");
            param.put("meterunit", "%" + (String) params.get("keyword") + "%");
            param.put("goodname", "%" + (String) params.get("keyword") + "%");
            param.put("goodorigin", "%" + (String) params.get("keyword") + "%");
        }
		return super.queryMapListBySql(sql, param);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.GoodSalesDaoExtension#getGoodSerialsMaxValBySettleidDao(java.lang.Long)
	 */
	@Override
	public Integer getGoodSerialsMaxValBySettleidDao(Long settleid) {
		String sql = " select goodserials from Goodsales where settleid = ?0 order by goodserials desc limit 1 ";
		Integer i = super.queryIntegerBySql(sql, settleid);
		return (i != null && i > 0) ? i + 1 : 1;
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.GoodSalesDaoExtension#getGoodSalesObjBySettleIdAndSpecIdDao(java.lang.Long, java.lang.Long)
	 */
	@Override
	public Goodsales getGoodSalesObjBySettleIdAndSpecIdDao(Long settleid, Long specid) {
		String sql = "from Goodsales where settleid = ?0 and specid = ?1 and entitystate = ?2 ";
		return super.queryEntity(sql, settleid, specid, EntityState.INVALID.getValue());
	}

	@Override
	public List<SingleGoodsExtension> getTheSingleGoodsExtension(Integer limitMin, Integer limitMax, String key, Long goodgroupid, String path, List<Long> classSearch, List<Long> merchatSearch, List<Long> marketSearch) {
		Map<String,Object> map = new HashMap<String, Object>();
		StringBuffer sql = new StringBuffer("select concat(g.Entityid) as goodentityid,g.GoodName as goodname,mt.MarketName as marketname,concat(mt.EntityId) as marketentityid,concat(ms.Entityid) as merchantsettleid,concat(m.EntityId) as merchantsentityid,m.MerchantName as merchantname "
				+ " ,(select CONCAT(:path,gphoto.PhotoPath) from GoodPhotos gphoto where gphoto.CoverPhoto = '1' and gphoto.EntityState = :enabled and gphoto.GoodId = g.entityid limit 1) as goodphoto "
				+ " ,(select gsale.GoodPrice from GoodSales gsale,GoodSpecs gspecs where gsale.SettleId = ms.entityid and gsale.SpecId = gspecs.EntityId and gsale.EntityState = :published and gspecs.GoodId = g.EntityId and gspecs.EntityState = :enabled order by gsale.GoodPrice asc limit 1) as goodprice "
				+ " ,(select sum(og.GoodNumber) from OrderGoods og,Orders os where og.OrderId = os.EntityId and og.EntityState = :enabled and os.EntityState = :finish and og.SaleId IN (select gsale.entityid from GoodSales gsale,GoodSpecs gspecs where gsale.SettleId = ms.entityid and gsale.SpecId = gspecs.EntityId and gsale.SettleId = ms.EntityId and gsale.EntityState = :published and gspecs.GoodId = g.EntityId and gspecs.EntityState = :enabled) ) as salenumber "
				+ " from Goods g,MerchantSettles ms,Merchants m,Markets mt "
				+ " where g.MerchantId = m.EntityId and m.EntityId = ms.MerchantId and ms.MarketId = mt.EntityId and g.EntityState = :enabled and ms.EntityState = :enabled and m.EntityState = :enabled and mt.EntityState = :enabled "
				+ " and ms.Entityid IN (select gsale.SettleId from GoodSales gsale,GoodSpecs gspecs where gsale.SpecId = gspecs.EntityId and gsale.EntityState = :published and gspecs.GoodId = g.EntityId and gspecs.EntityState = :enabled) ");
		map.put("path", path);
		map.put("enabled", EntityState.VALID.getValue());
		map.put("finish", EntityState.OTS.getValue());
		map.put("published", EntityState.PUBLISHED.getValue());
		if(!CommonUtil.isEmpty(goodgroupid)) {
			sql.append(" and g.GroupId = :groupid ");
			map.put("groupid", goodgroupid);
		}
		if(!CommonUtil.isEmpty(key)) {
			sql.append(" and g.GoodName like :key ");
			map.put("key", "%"+key+"%");
		}
		if(!CommonUtil.isEmpty(classSearch)) {
			sql.append(" and g.GroupId IN (:classSearch) ");
			map.put("classSearch", classSearch);
		}
		if(!CommonUtil.isEmpty(merchatSearch)) {
			sql.append(" and m.EntityId IN (:merchatSearch) ");
			map.put("merchatSearch", merchatSearch);
		}
		if(!CommonUtil.isEmpty(marketSearch)) {
			sql.append(" and mt.EntityId IN (:marketSearch) ");
			map.put("marketSearch", marketSearch);
		}
		sql.append(" group by goodentityid,goodname,marketname,marketentityid,merchantsentityid,merchantname,goodphoto,goodprice,salenumber ");
		sql.append(" limit :limitmin,:limitmax");
		map.put("limitmin",limitMin);
		map.put("limitmax",limitMax);
		return super.queryCustomBeanListBySql(SingleGoodsExtension.class, sql.toString(), map);
	}

	@Override
	public List<Map<String, Object>> getTheGoodsAllPhotos(Long goodentityid, String path) {
		Map<String,Object> map = new HashMap<String, Object>();
		String sql = " select CONCAT(:path,gp.PhotoPath) as goodphoto from GoodPhotos gp where gp.GoodId = :goodid and gp.EntityState = :istate order by CoverPhoto desc ";
		map.put("path", path);
		map.put("goodid", goodentityid);
		map.put("istate", EntityState.VALID.getValue());
		return super.queryMapListBySql(sql, map);
	}

	@Override
	public Map<String, Object> getTheGoodInfos(Long goodentityid,Long merchantsettleid) {
		Map<String,Object> map = new HashMap<String, Object>();
		String sql = " select concat(g.entityid) as goodentityid,concat(gsale.SettleId) as merchantsettleid,g.GoodName as goodname,g.GoodOrigin as goodorigin,g.GoodDesc as gooddesc "
				+ " ,gspecs.SpecDefine as specdefine,gsale.GoodPrice as goodprice,gsale.GoodStock as goodstock "
				+ " from Goods g, GoodSales gsale, GoodSpecs gspecs where g.entityid = :goodentityid and g.EntityState = :istate and gsale.SettleId= :merchantsettleid and gsale.SpecId = gspecs.EntityId and gsale.EntityState = :published and gspecs.GoodId = g.EntityId and gspecs.EntityState = :istate order by gsale.GoodPrice asc limit 1 ";
		map.put("goodentityid", goodentityid);
		map.put("merchantsettleid", merchantsettleid);
		map.put("published", EntityState.PUBLISHED.getValue());
		map.put("istate", EntityState.VALID.getValue());
		return super.queryMapBySql(sql, map);
	}

	@Override
	public List<Map<String, Object>> geTheAllGoodSpecs(Long goodid, Long merchantsettleid,String path) {
		Map<String,Object> map = new HashMap<String, Object>();
		String sql = " select concat(gsale.Entityid) as goodsaleid,gsale.GoodPrice as goodprice,gsale.GoodStock as goodstock,gsale.GoodNumber as goodnumber,gspecs.SpecDefine as specdefine "
				+ " ,(select CONCAT(:path,gphoto.PhotoPath) from GoodPhotos gphoto where gphoto.CoverPhoto = '1' and gphoto.EntityState = :istate and gphoto.GoodId = g.entityid limit 1) as goodphoto "
				+ " from GoodSpecs gspecs,GoodSales gsale,MerchantSettles ms,Goods g where gspecs.GoodId = g.entityid and gspecs.entityid = gsale.SpecId and gsale.SettleId = ms.entityid and gspecs.EntityState = :istate and g.EntityState = :istate and gsale.EntityState = :published and ms.EntityState = :istate "
				+ " and g.entityid = :goodid and ms.entityid = :merchantSettleid order by gsale.GoodPrice asc ";
		map.put("path", path);
		map.put("published", EntityState.PUBLISHED.getValue());
		map.put("istate", EntityState.VALID.getValue());
		map.put("goodid", goodid);
		map.put("merchantSettleid", merchantsettleid);
		return super.queryMapListBySql(sql, map);
	}

	/**
	 * @see club.coderleague.ilsp.dao.extension.GoodSalesDaoExtension#findBySettleid(java.lang.Long)
	 */
	@Override
	public List<Map<String, Object>> findBySettleid(Long settleid) {
		String sql = "select gs.entityid as entityid, concat(g.goodname, '[', gsa.specdefine, ']') as goodname, gs.goodprice as goodprice"
				+ " from goodsales gs"
				+ " inner join goodspecs gsa on gsa.entitystate = ?2 and gsa.entityid = gs.specid"
				+ " inner join goods g on g.entitystate = ?2 and g.entityid = gsa.goodid"
				+ " where gs.entitystate = ?1 and gs.settleid = ?0";
		return super.queryMapListBySql(sql, settleid, EntityState.PUBLISHED.getValue(), EntityState.VALID.getValue());
	}
	
}
